﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using OGS.Contracts;

namespace OGS.DataAccess
{
    /// <summary>
    /// Represents a class used for database related work of Category
    /// </summary>
    public class DACategory
    {

        private static readonly DACategory instance;

        /// <summary>
        /// Initializes a static instance of the DACategory class with no arguments.
        /// </summary>
        static DACategory()
        {
            instance = new DACategory();
        }

        /// <summary>
        /// Initializes a new instance of the DACategory class with no arguments.
        /// </summary>
        private DACategory() { }

        /// <summary>
        /// The Instance of DACategory class
        /// </summary>
        public static DACategory Instance
        {
            get
            {
                return instance;
            }
        }

        /// <summary>
        /// Saves the Category
        /// </summary>
        /// <param name="objCategoryInfo"> The CategoryInfo Object </param>
        /// <returns>Success/Faliure</returns>
        public int SaveCategory(Category objCategoryInfo)
        {
            DataTable dtCategoryLanguage = new DataTable();
            dtCategoryLanguage.Columns.Add("LanguageID");
            dtCategoryLanguage.Columns.Add("Name");

            foreach (CategoryLanguage CategoryLanguage in objCategoryInfo.CategoryLanguages)
            {
                DataRow dr = dtCategoryLanguage.NewRow();
                dr["LanguageID"] = CategoryLanguage.LanguageID;
                dr["Name"] = CategoryLanguage.Name;
                dtCategoryLanguage.Rows.Add(dr);
            }

            using (DBManager dbm = new DBManager("stp_Save_Category"))
            {
                dbm.Add("@CategoryID", objCategoryInfo.ID, ParameterDirection.InputOutput);
                dbm.Add("@Code", objCategoryInfo.Code);
                dbm.Add("@Url", objCategoryInfo.Url);
                dbm.Add("@Image", objCategoryInfo.Image);
                dbm.Add("@DisplayOrder", objCategoryInfo.DisplayOrder);
                dbm.Add("@RootLevel", objCategoryInfo.RootLevel);
                dbm.Add("@IsActive", objCategoryInfo.IsActive);
                dbm.Add("@ParentCategoryID", objCategoryInfo.ParentCategoryID);
                dbm.Add("@LastModifiedBy", objCategoryInfo.LastModifiedBy);

                dbm.Add("@CategoryLanguage", dtCategoryLanguage);

                dbm.ExecuteNonQuery();

                objCategoryInfo.ID = (int)dbm.GetValueInInt("@CategoryID");
                return Convert.ToInt32(dbm.ReturnValue);
            }
        }

        /// <summary>
        /// Get Category details
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public Category GetCategoryDetails(int ID)
        {
            DataTable dt = new DataTable();
            using (DBManager dbm = new DBManager("stp_Get_CategoryDetails"))
            {
                dbm.Add("@CategoryID", ID);

                dt = dbm.GetDataTable();
            }
            Category Category = new Category();

            if (dt.Rows.Count > 0)
            {
                Category = PopulateCategory(dt);
            }

            return Category;
        }

        /// <summary>
        /// Get Categories
        /// </summary>
        /// <param name="ParentCategoryID"></param>
        /// <returns></returns>
        public List<Category> GetCategories(int ParentCategoryID)
        {
            DataTable dt = new DataTable();
            List<Category> Categories = new List<Category>();
            using (DBManager dbm = new DBManager("stp_Get_Categories"))
            {
                dbm.Add("@ParentCategoryID", ParentCategoryID);
                dt = dbm.GetDataTable();
            }

            for (int count = 0; count < dt.Rows.Count; )
            {
                Category Category = new Category { ID = Convert.ToInt32(dt.Rows[count]["PK_CategoryID"]) };
                Category.CategoryLanguages = new List<CategoryLanguage>();
                Category.CategoryLanguages.Add(new CategoryLanguage
                {
                    ID = Convert.ToInt32(dt.Rows[count]["PK_CategoryID"]),
                    Name = Convert.ToString(dt.Rows[count]["Name"]),
                    LanguageID = Convert.ToInt32(dt.Rows[count]["LanguageID"])
                });
                Category.CategoryLanguages.Add(new CategoryLanguage
                {
                    ID = Convert.ToInt32(dt.Rows[count + 1]["PK_CategoryID"]),
                    Name = Convert.ToString(dt.Rows[count + 1]["Name"]),
                    LanguageID = Convert.ToInt32(dt.Rows[count + 1]["LanguageID"])
                });
                Category.CategoryLanguages.Add(new CategoryLanguage
                {
                    ID = Convert.ToInt32(dt.Rows[count + 2]["PK_CategoryID"]),
                    Name = Convert.ToString(dt.Rows[count + 2]["Name"]),
                    LanguageID = Convert.ToInt32(dt.Rows[count + 2]["LanguageID"])
                });

                Category.ComputedName = Category.CategoryLanguages[0].Name + " (" + Category.CategoryLanguages[1].Name + ") ";

                Categories.Add(Category);

                count = count + 3;
            }

            return Categories;
        }

        /// <summary>
        /// Search Category
        /// </summary>
        /// <param name="objCategoryInfo"></param>
        /// <param name="objSearch"></param>
        /// <returns></returns>
        public List<Category> SearchCategory(Category objCategoryInfo, SearchAttributes objSearch)
        {
            DataTable dt = new DataTable();
            using (DBManager dbm = new DBManager("stp_Search_Category", objSearch))
            {
                if (objCategoryInfo.ParentCategoryID >= 0)
                {
                    dbm.Add("@ParentCategoryID", objCategoryInfo.ParentCategoryID);
                }
                if (objCategoryInfo.CategoryLanguages[0].Name != string.Empty)
                {
                    dbm.Add("@Name", objCategoryInfo.CategoryLanguages[0].Name);
                }
                //dbm.Add("@IsActive", objBrandInfo.IsActive);
                dt = dbm.GetDataTable();
                objSearch.TotalRecordCount = Convert.ToInt32(dbm.GetValueInInt("@TotalRecordCount"));
            }

            List<Category> lstCategory = new List<Category>();
            for (int count = 0; count < dt.Rows.Count; )
            {

                Category objCategory = PopulateCategory(dt.Rows[count]);

                objCategory.CategoryLanguages = new List<CategoryLanguage>();
                objCategory.CategoryLanguages.Add(new CategoryLanguage
                {
                    ID = Convert.ToInt32(dt.Rows[count]["PK_CategoryID"]),
                    Name = Convert.ToString(dt.Rows[count]["Name"]),
                    LanguageID = Convert.ToInt32(dt.Rows[count]["LanguageID"])
                });
                objCategory.CategoryLanguages.Add(new CategoryLanguage
                {
                    ID = Convert.ToInt32(dt.Rows[count + 1]["PK_CategoryID"]),
                    Name = Convert.ToString(dt.Rows[count + 1]["Name"]),
                    LanguageID = Convert.ToInt32(dt.Rows[count + 1]["LanguageID"])
                });
                objCategory.CategoryLanguages.Add(new CategoryLanguage
                {
                    ID = Convert.ToInt32(dt.Rows[count + 2]["PK_CategoryID"]),
                    Name = Convert.ToString(dt.Rows[count + 2]["Name"]),
                    LanguageID = Convert.ToInt32(dt.Rows[count + 2]["LanguageID"])
                });

                lstCategory.Add(objCategory);
                count = count + 3;

            }
            return lstCategory;
        }

        /// <summary>
        /// Populate the Category
        /// </summary>
        /// <param name="dt"> The CategoryInfo DataTable </param>
        /// <returns>The Category Object </returns>
        private Category PopulateCategory(DataTable dt)
        {
            Category objCategoryInfo = new Category();
            DataRow dr = dt.Rows[0];

            objCategoryInfo.ID = Convert.ToInt32(dr["PK_CategoryID"]);
            objCategoryInfo.Code = Convert.ToString(dr["Code"]);
            objCategoryInfo.Url = Convert.ToString(dr["Url"]);
            objCategoryInfo.Image = Convert.ToString(dr["Image"]);
            objCategoryInfo.DisplayOrder = Convert.ToInt32(dr["DisplayOrder"]);
            objCategoryInfo.ParentCategoryID = Convert.ToInt32(dr["ParentCategoryID"]);
            objCategoryInfo.IsActive = Convert.ToBoolean(dr["IsActive"]);
            objCategoryInfo.RootLevel = Convert.ToInt16(dr["RootLevel"]);
            objCategoryInfo.LastModifiedDate = Convert.ToDateTime(dr["LastModifiedDate"]);
            objCategoryInfo.LastModifiedBy = Convert.ToInt32(dr["LastModifiedBy"]);

            foreach (DataRow drLang in dt.Rows)
            {
                objCategoryInfo.CategoryLanguages.Add(new CategoryLanguage
                {
                    ID = Convert.ToInt32(drLang["PK_CategoryID"]),
                    LanguageID = Convert.ToInt32(drLang["LanguageID"]),
                    Name = Convert.ToString(drLang["Name"])
                });
            }

            return objCategoryInfo;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private Category PopulateCategory(DataRow dr)
        {
            Category objCategoryInfo = new Category();
            objCategoryInfo.ID = Convert.ToInt32(dr["PK_CategoryID"]);
            objCategoryInfo.Code = Convert.ToString(dr["Code"]);
            objCategoryInfo.Url = Convert.ToString(dr["Url"]);
            objCategoryInfo.Image = Convert.ToString(dr["Image"]);
            objCategoryInfo.DisplayOrder = Convert.ToInt32(dr["DisplayOrder"]);
            objCategoryInfo.ParentCategoryID = Convert.ToInt32(dr["ParentCategoryID"]);
            objCategoryInfo.IsActive = Convert.ToBoolean(dr["IsActive"]);
            objCategoryInfo.RootLevel = Convert.ToInt16(dr["RootLevel"]);
            objCategoryInfo.ParentCategoryName = Convert.ToString(dr["ParentCategoryName"]);

            return objCategoryInfo;
        }

    }
}